﻿using System;
using System.Data;
using System.Data.Common;

namespace MySql.Data.MySqlClient
{
    /// <summary>
    /// 执行查询数据库的帮手类
    /// </summary>
    public class MySQLHelper
    {
        private static MySQLHelper instance = new MySQLHelper();

        public static MySQLHelper Instance
        {
            get
            {
                return instance;
            }
        }
        private string SetConnectionString(string server, string user, string pwd, string db)
        {
            return string.Format("Server={0};Database={1};Uid={2};Pwd={3};", server, db, user, pwd);
        }
        private string server;
        /// <summary>
        /// 数据库服务器
        /// </summary>
        public string Server
        {
            get { return this.server; }
            set
            {
                if (this.server != value)
                {
                    this.server = value;
                    this.ConnectionString = this.SetConnectionString(this.server, this.user, this.password, this.database);
                }
            }
        }
        private string user;
        /// <summary>
        /// 用户名
        /// </summary>
        public string User
        {
            get { return this.user; }
            set
            {
                if (this.user != value)
                {
                    this.user = value;
                    this.ConnectionString = this.SetConnectionString(this.server, this.user, this.password, this.database);
                }
            }
        }
        private string password;
        /// <summary>
        /// 密码
        /// </summary>
        public string Password
        {
            get { return this.password; }
            set
            {
                if (this.password != value)
                {
                    this.password = value;
                    this.ConnectionString = this.SetConnectionString(this.server, this.user, this.password, this.database);
                }
            }
        }
        private string database;
        /// <summary>
        /// 数据库名称
        /// </summary>
        public string Database
        {
            get { return this.database; }
            set
            {
                if (this.database != value)
                {
                    this.database = value;
                    this.ConnectionString = this.SetConnectionString(this.server, this.user, this.password, this.database);
                }
            }
        }
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString { get; private set; }
        /// <summary>
        /// 创建数据库连接对象
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConnection()
        {
            return CreateConnection(ConnectionString);
        }
        /// <summary>
        /// 创建数据库连接对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <returns></returns>
        public DbConnection CreateConnection(string connectionString)
        {
            return new MySqlConnection(connectionString);
        }
        /// <summary>
        /// 测试连接
        /// </summary>
        /// <returns></returns>
        public bool TestConnect()
        {
            try
            {
                using (DbConnection conn = CreateConnection())
                {
                    conn.Open();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// 执行数据库查询，不包括SELECT语句
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">绑定命令的参数集</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    using (MySqlConnection connection = new MySqlConnection(connectionString))
                    {
                        PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                        int val = cmd.ExecuteNonQuery();
                        return val;
                    }
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 使用存在的数据库连接对象执行SQLite命令，不返回记录集
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));
        /// </remarks>
        /// <param name="connection">存在的数据库连接对象</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>返回受影响的记录数</returns>
        public int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 使用存在的数据库事务执行SQLite命令，不返回记录集
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));
        /// </remarks>
        /// <param name="trans">存在的数据库事务</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>返回受影响的记录数</returns>
        public int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                    int val = cmd.ExecuteNonQuery();
                    return val;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 执行SELECT语句查询返回记录集
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                MySqlConnection conn = new MySqlConnection(connectionString);
                try
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                    DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    return rdr;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 执行SELECT语句查询返回记录集
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    DbDataReader rdr = cmd.ExecuteReader();
                    return rdr;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 执行SELECT语句查询返回记录集
        /// </summary>
        /// <param name="trans">存在数据库事务</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                    DbDataReader rdr = cmd.ExecuteReader();
                    return rdr;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 用存在数据库字符串执行查询，并将查询返回的结果集中第一行的第一列作为数据类型返回
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));
        /// </remarks>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                using (MySqlConnection conn = new MySqlConnection(connectionString))
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                        object val = cmd.ExecuteScalar();
                        return val;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();
                    }
                }
            }
        }
        ///	<summary>
        /// 用存在数据库事务执行查询，并将查询返回的结果集中第一行的第一列作为数据类型返回
        ///	</summary>
        ///	<param name="trans">存在数据库事务</param>
        ///	<param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        ///	<param name="cmdText">执行的SQL语句</param>
        ///	<param name="commandParameters">用于执行这命令的SQLite参数集</param>
        ///	<returns>数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
        public object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            if (trans == null)
                throw new ArgumentNullException("transaction");
            if (trans != null && trans.Connection == null)
                throw new ArgumentException("The transaction was rollbacked	or commited, please	provide	an open	transaction.", "transaction");

            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
                    object retval = cmd.ExecuteScalar();
                    return retval;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 用存在数据库连接对象执行查询，并将查询返回的结果集中第一行的第一列作为数据类型返回
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));
        /// </remarks>
        /// <param name="connection">存在数据库连接对象</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用</returns>
        public object ExecuteScalar(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    object val = cmd.ExecuteScalar();
                    return val;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 执行SELECT语句查询返回记录集
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                using (MySqlConnection conn = new MySqlConnection(connectionString))
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                        DbDataAdapter da = new MySqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }
                    finally
                    {
                        cmd.Parameters.Clear();
                    }
                }
            }
        }
        /// <summary>
        /// 执行SELECT语句查询返回记录集
        /// </summary>
        /// <param name="connection">存在数据库连接对象</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

                    DbDataAdapter da = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 执行SELECT语句查询返回记录集
        /// </summary>
        /// <param name="trans">存在数据库事务</param>
        /// <param name="cmdType">执行类型；如：存储过程、SQL语句</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);

                    DbDataAdapter da = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                finally
                {
                    cmd.Parameters.Clear();
                }
            }
        }
        /// <summary>
        /// 检查记录是否存在，如：select count(*) from table
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>存在记录返回true，否则返回false</returns>
        public bool RecordIsExist(string connectionString, string cmdText, params DbParameter[] commandParameters)
        {
            using (DataSet dt = ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters))
            {
                return (Convert.ToInt64(dt.Tables[0].Rows[0][0]) != 0);
            }
        }
        /// <summary>
        /// 检查记录是否存在，如：select count(*) from table
        /// </summary>
        /// <param name="connection">存在数据库连接对象</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>存在记录返回true，否则返回false</returns>
        public bool RecordIsExist(DbConnection connection, string cmdText, params DbParameter[] commandParameters)
        {
            using (DataSet dt = ExecuteDataSet(connection, CommandType.Text, cmdText, commandParameters))
            {
                return (Convert.ToInt64(dt.Tables[0].Rows[0][0]) != 0);
            }
        }
        /// <summary>
        /// 检查记录是否存在，如：select count(*) from table
        /// </summary>
        /// <param name="trans">存在数据库事务</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        /// <returns>存在记录返回true，否则返回false</returns>
        public bool RecordIsExist(DbTransaction trans, string cmdText, params DbParameter[] commandParameters)
        {
            using (DataSet dt = ExecuteDataSet(trans, CommandType.Text, cmdText, commandParameters))
            {
                return (Convert.ToInt64(dt.Tables[0].Rows[0][0]) != 0);
            }
        }
        /// <summary>
        /// 获取SQL分页语句
        /// </summary>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="pageIndex">当前页码，从1开始</param>
        /// <param name="pageSize">每页记录数</param>
        /// <returns></returns>
        public string GetSQLPaging(string cmdText, int pageIndex, int pageSize)
        {
            return string.Format("select * from ({0}) T where limit {2} offset ({1}-1)*{2}", cmdText, pageIndex, pageSize);
        }
        /// <summary>
        /// 预处理Command对象,数据库链接,事务,需要执行的对象,参数等的初始化
        /// </summary>
        /// <param name="cmd">Command对象</param>
        /// <param name="conn">Connection对象</param>
        /// <param name="trans">Transcation对象</param>
        /// <param name="cmdType">SQL字符串执行类型</param>
        /// <param name="cmdText">执行的SQL语句</param>
        /// <param name="commandParameters">用于执行这命令的SQLite参数集</param>
        private void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] commandParameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (trans != null)
                cmd.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (DbParameter parm in commandParameters)
                {
                    parm.ParameterName = parm.ParameterName;
                    cmd.Parameters.Add(parm);
                }
            }
        }
    }
}